USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetRelationsXml')
	DROP FUNCTION dbo.GetRelationsXml
GO


CREATE FUNCTION [dbo].[GetRelationsXml] (@sourceGuid varchar(100))
RETURNS VARCHAR(MAX)
AS
BEGIN

	--	RelationshipType	TypeDesc
	--	1	Reseller
	--	2	Contact
	--	3	Person Responsible
	--	4	Distributor
	--	5	Manager
	--	6	Child Account
	--	7	Parent Account
	--	8	Account
	--	9	Activity Partner
	--	10	Task Partner
	--	11	Opportunity Partner
	
	declare 
		@relations			varchar(max)

	SET @relations		= '<RELATIONS>'

	;WITH A AS(
		-- GET OWNER/PARENT_ACCOUNT/RELATED_PARTNER/ACTIVITY/TASK
		SELECT
			RelationShipID			as ID
		,	ISMain					as Main
		,	RelationShipType		AS TypeID
		,	(CASE 
				WHEN RelationshipType=1		THEN 'RESELLER'
				WHEN RelationshipType=2		THEN 'CONTACT'
				WHEN RelationshipType=3		THEN 'OWNER'
				WHEN RelationshipType=7		THEN 'PARENT_ACCOUNT'
				WHEN RelationshipType=8		THEN 'ACCOUNT'
				WHEN RelationshipType=9		THEN 'ACTIVITY_ACCOUNT'
				WHEN RelationshipType=10	THEN 'TASK_ACCOUNT'
				WHEN RelationshipType=11	THEN 'OPPORTUNITY_ACCOUNT'
			END	)					AS [Type]
		,	Partner2				as Partner2
		FROM TR_RELATIONSHIPS as r WITH (NOLOCK)
		WHERE 
			CAST(SourceGUID AS VARCHAR(100)) = ISNULL(@sourceGuid, '')
		AND r.RelationshipType IN (1,2,3,7,8,9,10,11)
	), B AS(
		-- GET PRIMARY CONTACT
		SELECT
			RelationShipID			as ID
		,	ISMain					as Main
		,	RelationShipType		AS TypeID
		,	'PRIMARY_CONTACT'		AS [Type]
		,	Partner2				as Partner2
		FROM TR_RELATIONSHIPS as r WITH (NOLOCK)
		WHERE 
			CAST(SourceGUID AS VARCHAR(100)) = ISNULL(@sourceGuid, '')
		AND r.RelationshipType = 2
		AND IsMain = 1
	), Final AS (
		SELECT ID, Main, TypeID, [Type], [Partner2] FROM A
		UNION ALL
		SELECT ID, Main, TypeID, [Type], [Partner2] FROM B
	)

	SELECT @relations = @relations + '<RELATION id="'+CAST(ID AS VARCHAR)+'" '+
										'main="'+ ISNULL(CAST(Main as VARCHAR),'') +'" '+
										'typeid="'+ ISNULL(CAST([TypeID] AS VARCHAR),'0') +'" ' +
										'type="' +	[Type]	+ '" >'+
										'<![CDATA['+ISNULL(CAST(Partner2 AS VARCHAR),'0')+']]></RELATION>' 
	FROM Final


	-- CLOSING TAG
	SET @relations = @relations + '</RELATIONS>'

	return @relations

END
